package com.deng.auto;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import com.deng.common.SQLHelper;

public class ImportEmployeeXHT  {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		try{
			new ImportEmployeeXHT().go();
		}catch(Exception ex){
			ex.printStackTrace();
		}
	}
	 protected static Connection getConnect() {
		 try{        
			 Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");      
			 Connection con=DriverManager.
				getConnection("jdbc:sqlserver://localhost:1433;DatabaseName=PZSYYDB","sa","sa");
			return con;       
		 }catch(Exception ex){        
			 ex.printStackTrace();        
		 }        
		 return null;  
	}
	 protected ResultSet queryData(Connection conn,String sql){
			System.out.println("::::queryData:::::   "+sql);
			PreparedStatement pstmt = null;
			try{
				pstmt = conn.prepareStatement(sql);
				ResultSet rs = pstmt.executeQuery();
				return rs;
			}catch(Exception ex){
				ex.printStackTrace();	
			}
			return null;
		}
	public void go(){
		try{
			String sql = "select * from xht";
			Connection conn = getConnect();
			conn.setAutoCommit(false);
			
			ResultSet rs = queryData(conn, sql);
			
			String insertSQL = "insert into t_sys_stat_employee" +
			"	(name," +//姓名
			"xingbie," +//性别
			"sfz," +//sfz
			"xztm," +//薪资条码
			"his_id," +//his id
			"work_id," +//gonghao 
			"in_use," +//in use
			"shenfen," +//身份
			"renyuanxingzhi," +//人员性质
			"shifoufanpin," +//是否返聘
			"yonggongzhidu," +//用工制度
			"daoshixingming," +//导师姓名
			"xingzhengkeshi," +//行政科室
			"keshixingzhi," +//科室性质
			"gongzuojixiaohesuandanyuan," +//gongzuo jixiao hesuan danyuan 
			"jingyingjixiaohesuandanyuan," +//jingying jiixoa
			"zhiweibiaozhunfen," +//职位标准分
			"fengxianxishu," +//风险系数
			"xingzhengzhiwu1," +//行政职务1
			"xingzhengzhiwu2," +//行政职务2
			"xingzhengjibie," +//行政级别
			"xingzhengshizhi," +//行政实职
			"chushengriqi," +//出生日期
			"ruzhishijian," +//入职时间
			"ruyuanshijian," +//入院时间
			"gangweimingcheng," +//岗位名称
			"gangweijibie," +//岗位级别
			"gangweileibie," +//岗位类别
			"linchuangjishuma," +//临床技术吗
			"jishuzhicheng," +//技术职称
			"xianpinzhicheng," +//现聘职称
			"zhichengjibie," +//职称级别
			"zhiyeleibie," +//职业类别
			"teshubiaozhi," +//特殊标志
			"zhuguankeshi," +//主管科室
			"fenguankeshi," +//分管科室
			"gongzuojixiaogangweixishu," +//工作绩效岗位系数
			"jingyingjixiaogangweixishu,employee_id)" +//经营绩效岗位系数
			"values(?,?,?,?,?,?,?,?,?,?," +
			"?,?,?,?,?,?,?,?,?,?," +
			"?,?,?,?,?,?,?,?,?,?," +
			"?,?,?,?,?,?,?,?,?)" ;
			PreparedStatement pst =conn.prepareStatement(insertSQL);
			int ct = 1017;
			while(rs.next()){
				
				String 奖金月份 = rs.getString("奖金月份");
				String 出勤状态 = rs.getString("出勤状态");
				String 缺勤天数 = rs.getString("缺勤天数");
				String 出勤天数 = rs.getString("出勤天数");
				String 本月总天数 = rs.getString("本月总天数");
				String 奖金科室 = rs.getString("奖金科室");
				String 姓名 = rs.getString("姓名");
				String 基本工资 = rs.getString("基本工资");
				String 职位标准分 = rs.getString("职位标准分");
				String 权重系数 = rs.getString("权重系数");
				String 出勤率 = rs.getString("出勤率");
				String 全额出勤分 = rs.getString("全额出勤分");
				String 奖金比率 = rs.getString("奖金比率");
				String 实际出勤分 = rs.getString("实际出勤分");
				String 奖金分值 = rs.getString("奖金分值");
				String 奖金应发 = rs.getString("奖金应发");
				String 补发 = rs.getString("补发");
				String 扣款 = rs.getString("扣款");
				String 奖金备注 = rs.getString("奖金备注");
				String 税前奖金 = rs.getString("税前奖金");
				String 季度尾数 = rs.getString("季度尾数");
				String 尾数补发 = rs.getString("尾数补发");
				String 尾数扣款 = rs.getString("尾数扣款");
				String 尾数备注 = rs.getString("尾数备注");
				String 税前尾数 = rs.getString("税前尾数");
				String 奖金合计 = rs.getString("奖金合计");
				String 导盘数额 = rs.getString("导盘数额");
				String 科室性质 = rs.getString("科室性质");
				String 人事类别 = rs.getString("人事类别");
				String 职称类别 = rs.getString("职称类别");
				String 人员性质 = rs.getString("人员性质");
				String 工资薪金码 = rs.getString("工资薪金码");
				String 医院工号 = rs.getString("医院工号");
				String 性别 = rs.getString("性别");
				String 现聘职称 = rs.getString("现聘职称");
				String 职业类别 = rs.getString("职业类别");
				String 行政职务1 = rs.getString("行政职务1");
				String 临床技术码 = rs.getString("临床技术码");
				String 职称简码 = rs.getString("职称简码");
				String 身份证号码 = rs.getString("身份证号码");
				String xzks_code = rs.getString("xzks_code");
				
				pst.setString(1, 姓名);
				if(性别!=null){//1nan 2nv
					if(性别.trim().equals("男")){
						pst.setInt(2, 1);
					}else{
						pst.setInt(2, 2);
					}
				}
				
				pst.setString(3, 身份证号码);
				pst.setString(4, 工资薪金码);
				pst.setString(5, null);
				pst.setString(6, 医院工号);
				pst.setInt(7,1);
				pst.setInt(8, 0);//
				if(人员性质 != null){//(h合同2  z在编1  t退休0 )        否返聘0   是返聘1
					if(人员性质.trim().equals("h")){
						pst.setInt(9, 2);
						pst.setInt(10, 0);
					}else if(人员性质.trim().equals("z")){
						pst.setInt(9, 1);
						pst.setInt(10, 0);
					}else{
						pst.setInt(9, 0);
						pst.setInt(10, 1);
					}
				}
				
				pst.setInt(11, 3);
				pst.setString(12, null);//导师
				Integer ai = getAdminId(conn,xzks_code);
				if(ai != null)	
					pst.setInt(13, ai);
				
				if(科室性质!= null){//DICT_keshixingzhi
					if(科室性质.trim().equals("医疗技术")){
						pst.setInt(14, 1);
					}else if(科室性质.trim().equals("行政后勤")){
						pst.setInt(14, 2);
					}else{
						pst.setInt(14, 0);
					}
				}else{
					pst.setInt(14, 0);
				}
				
				pst.setInt(15, 0);//工作绩效核算单元
				pst.setInt(16, 0);//经营绩效核算单元
				if(职位标准分 != null){
					pst.setBigDecimal(17, new BigDecimal(职位标准分));
				}
				
				pst.setBigDecimal(18, new BigDecimal(1));//风险系数
				pst.setInt(19, 0);//行政职务1
				pst.setInt(20, 0);//行政职务2
				pst.setInt(21, 0);//行政级别
				pst.setInt(22, 0);//行政实职
				pst.setDate(23, null);//出生日期
				pst.setDate(24, null);//入职日期
				pst.setDate(25, null);//入院时间
				pst.setInt(26, 0);//岗位名称
				pst.setInt(27, 0);//岗位级别
				pst.setInt(28, 0);//岗位类别
				
				pst.setString(29, 临床技术码);
				pst.setInt(30, 0);//技术职称
				pst.setInt(31, 0);//现聘职称
				pst.setInt(32, 0);//职称级别
				Integer x = getDutyId(conn,职业类别);
				if(x != null){
					pst.setInt(33, x);//职业类别
				}else{
					pst.setInt(33, 0);
				}
				
				pst.setString(34, null);//特殊标志
				pst.setString(35, null);//主管科室
				pst.setString(36, null);//分管科室
				pst.setBigDecimal(37, new BigDecimal(1));//工作绩效岗位系数
				pst.setBigDecimal(38, new BigDecimal(1));//经营绩效岗位系数
				pst.setInt(39, ct);
				
				pst.addBatch();
				ct++;
			}
			pst.executeBatch();
			conn.commit();
			pst.close();
			conn.close();
		}catch(Exception ex){
			ex.printStackTrace();
		}
	}
	
	private Integer getAdminId(Connection conn,String code){
		try{
			if(code == null){
				return null;
			}
			String sql = "select * from t_sys_stat_admin_department where code = '"+code+"'";
			ResultSet rs = queryData(conn, sql);
			if(rs.next()){
				return rs.getInt("id");
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	}
	
	private Integer getDutyId(Connection conn,String name){
		try{
			if(name == null){
				return null;
			}
			String sql = "select * from DICT_DUTY_TYPE where name = '"+name+"'";
			ResultSet rs = queryData(conn, sql);
			if(rs.next()){
				return rs.getInt("id");
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	}
	
}
